USE SAS
GO


IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'SearchOpportunities')
	BEGIN
		DROP  Procedure  dbo.SearchOpportunities
	END

GO

CREATE Procedure dbo.SearchOpportunities
	@text			varchar(max)
,	@userId			VARCHAR(10)
AS
BEGIN

	;WITH A AS (
		-- OPPORTUNITY HEADER
		SELECT
			op.OpportunityId				AS OpportunityId
		,	op.[Description]				AS OpportunityName
		,	opt.TypeDesc					AS OpportunityType
		,	op.OppRevenue					AS Revenue
		,	op.Probability
		,	op.MilestoneDate				AS MileStone
		,	ops.StatusDesc					AS [Status]
		,	op.TotalQuantity	
		,	op.OppGuid						AS PKID
		FROM TR_OPPORTUNITYH as op WITH (NOLOCK)
		INNER JOIN TR_Relationships as r
			ON r.SourceGUID = op.OppGuid
		LEFT OUTER JOIN TR_OPPTYPE AS opt WITH (NOLOCK)
			ON opt.OppType = op.OppType
		LEFT OUTER JOIN TR_STATUS as ops WITH (NOLOCK)
			on ops.Status = op.Status
		WHERE
			r.Partner2 = @userID
		AND r.RelationshipType = 3		-- OWNER
	), B AS (
		-- ACCOUNT
		SELECT
			acc.PartnerName					AS AccountName
		,	A.PKID
		FROM TR_PARTNER AS acc WITH (NOLOCK)
		INNER JOIN TR_RELATIONSHIPS AS r WITH (NOLOCK)
			ON r.PARTNER2 = acc.PartnerNo
		INNER JOIN A
			ON A.PKID = R.SourceGUID
		WHERE
			r.RelationshipType = 11 -- OPPORTUNITY PARTNER
	), FINAL AS (
		SELECT
			A.OpportunityId				AS [Opportunity Id]
		,	A.OpportunityName			AS [Description]
		,	B.AccountName				AS [Account Name]
		,	A.OpportunityType			As [Opportunity Type]
		,	A.Revenue
		,	A.Probability
		,	CONVERT(VARCHAR(10), A.Milestone, 101) AS Milestone
		,	A.Status
		,	A.TotalQuantity
		,	A.PKID
		FROM A
		LEFT OUTER JOIN B
			ON B.PKID = A.PKID
	)

	SELECT * FROM FINAL
	Order BY Milestone DESC, [Description] ASC
END
GO
